package com.company.service;

import com.company.exception.TaskConflictException;
import com.company.model.Task;
import com.company.util.DBUtil;
import org.joda.time.DateTime;
import org.joda.time.format.DateTimeFormat;
import org.joda.time.format.DateTimeFormatter;

import java.io.FileWriter;
import java.io.IOException;
import java.sql.*;
import java.util.*;

public class TaskServiceImpl implements TaskService {

    // SQL查询参数化，避免SQL注入
    private static final String SQL_INSERT_TASK = "INSERT INTO tasks (title, content, type, project_id, priority, start_time, end_time, status, created_time, updated_time) " +
            "VALUES (?, ?, ?, ?, ?, ?, ?, ?, NOW(), NOW())";
    private static final String SQL_SELECT_BY_DATE = "SELECT * FROM tasks WHERE DATE(start_time) = ? ORDER BY start_time";
    private static final String SQL_SELECT_UPCOMING = "SELECT * FROM tasks WHERE start_time BETWEEN NOW() AND DATE_ADD(NOW(), INTERVAL ? MINUTE) AND status != '已完成' ORDER BY start_time";
    private static final String SQL_SELECT_CONFLICT = "SELECT 1 FROM tasks WHERE ( end_time < ? AND start_time > ?) LIMIT 1";
    private static final String SQL_SELECT_ALL_SORTED = "SELECT * FROM tasks ORDER BY start_time, priority, end_time, id";
    private static final String SQL_SELECT_WEEKLY_STATS = "SELECT status, COUNT(*) as count FROM tasks WHERE YEARWEEK(start_time, 1) = YEARWEEK(?, 1) GROUP BY status";
    private static final String SQL_SELECT_BY_ID = "SELECT * FROM tasks WHERE id = ?";

    //添加任务到数据库
    @Override
    public void addTask(Task task) throws TaskConflictException {
        // 检查任务时间冲突
        if (hasTimeConflict(task)) {
            throw new TaskConflictException("Error: New task conflicts with an existing task.");
        }

        // 建立数据库连接并准备插入语句
        try (Connection conn = DBUtil.getConnection();
             PreparedStatement pstmt = conn.prepareStatement(SQL_INSERT_TASK, Statement.RETURN_GENERATED_KEYS)) {

            // 设置SQL参数
            pstmt.setString(1, task.getTitle());
            pstmt.setString(2, task.getContent());
            pstmt.setString(3, task.getTaskType().name());
            pstmt.setInt(4, task.getProjectId());
            pstmt.setString(5, task.getPriority().name());
            pstmt.setTimestamp(6, new Timestamp(task.getStartTime().getMillis()));
            pstmt.setTimestamp(7, new Timestamp(task.getEndTime().getMillis()));
            pstmt.setString(8, task.getStatus().name());

            // 执行插入操作
            pstmt.executeUpdate();

            // 获取生成的主键ID并设置到任务对象中
            try (ResultSet rs = pstmt.getGeneratedKeys()) {
                if (rs.next()) {
                    task.setId(rs.getInt(1));
                }
            }
            System.out.println("Task added successfully: " + task.getTitle());

        } catch (SQLException e) {
            throw new RuntimeException("Database error while adding task.", e);
        }
    }


    //检查新任务是否与现有任务存在时间冲突

    private boolean hasTimeConflict(Task newTask) {
        try (Connection conn = DBUtil.getConnection();
             PreparedStatement pstmt = conn.prepareStatement(SQL_SELECT_CONFLICT)) {

            // 设置查询参数：检查在新任务时间范围内是否已存在任务
            pstmt.setTimestamp(1, new Timestamp(newTask.getStartTime().getMillis()));
            pstmt.setTimestamp(2, new Timestamp(newTask.getEndTime().getMillis()));

            try (ResultSet rs = pstmt.executeQuery()) {
                return rs.next(); // 如果有结果，说明存在冲突
            }
        } catch (SQLException e) {
            throw new RuntimeException("Database error while checking for conflicts.", e);
        }
    }

    //根据指定日期获取任务列表

    @Override
    public Task getTaskById(int taskId) {
        try (Connection conn = DBUtil.getConnection();
             PreparedStatement pstmt = conn.prepareStatement(SQL_SELECT_BY_ID)) {

            pstmt.setInt(1, taskId);

            try (ResultSet rs = pstmt.executeQuery()) {
                if (rs.next()) {
                    return mapResultSetToTask(rs);
                }
            }
        } catch (SQLException e) {
            throw new RuntimeException("Database error while fetching task by ID.", e);
        }
        return null;
    }

    //根据指定日期获取任务列表

    @Override
    public List<Task> getTasksByDate(DateTime date) {
        List<Task> tasks = new ArrayList<>();
        // 建立数据库连接并执行查询
        try (Connection conn = DBUtil.getConnection();
             PreparedStatement pstmt = conn.prepareStatement(SQL_SELECT_BY_DATE)) {

            pstmt.setDate(1, new java.sql.Date(date.getMillis()));

            // 处理查询结果集
            try (ResultSet rs = pstmt.executeQuery()) {
                while (rs.next()) {
                    tasks.add(mapResultSetToTask(rs));
                }
            }
        } catch (SQLException e) {
            throw new RuntimeException("Database error while fetching tasks by date.", e);
        }
        return tasks;
    }


    //获取指定分钟数内即将到期的任务列表

    @Override
    public List<Task> getUpcomingTasks(int minutes) {
        List<Task> tasks = new ArrayList<>();
        // 从数据库连接池获取连接并执行查询
        try (Connection conn = DBUtil.getConnection();
             PreparedStatement pstmt = conn.prepareStatement(SQL_SELECT_UPCOMING)) {

            pstmt.setInt(1, minutes);

            // 遍历查询结果，将结果集映射为Task对象并添加到列表中
            try (ResultSet rs = pstmt.executeQuery()) {
                while (rs.next()) {
                    tasks.add(mapResultSetToTask(rs));
                }
            }
        } catch (SQLException e) {
            throw new RuntimeException("Database error while fetching upcoming tasks.", e);
        }
        return tasks;
    }

    //获取所有任务，并按照时间排序。使用TreeSet自动根据Task的compareTo方法进行排序。

    @Override
    public Set<Task> getAllTasksSortedByTime() {
        Set<Task> tasks = new TreeSet<>((t1, t2) -> {
            int timeCompare = t1.getStartTime().compareTo(t2.getStartTime());
            if (timeCompare != 0) {
                return timeCompare;
            }
            return Integer.compare(t1.getId(), t2.getId());
        });

        try (Connection conn = DBUtil.getConnection();
             PreparedStatement pstmt = conn.prepareStatement(SQL_SELECT_ALL_SORTED);
            ResultSet rs = pstmt.executeQuery()) {

            while (rs.next()) {
             tasks.add(mapResultSetToTask(rs));
            }
        } catch (SQLException e) {
            throw new RuntimeException("Database error while fetching all tasks.", e);
        }
        return tasks;
    }


    //获取指定年份和周数的任务统计信息。统计内容包括总任务数、已完成任务数等。
    @Override
    public Map<String, Integer> getWeeklyStats(int year, int week) {
        Map<String, Integer> stats = new HashMap<>();
        stats.put("total", 0);
        stats.put("completed", 0);
        stats.put("overdue", 0);

        // 获取指定周的任意一天，用于查询
        DateTime weekDate = new DateTime(year, 1, 1, 0, 0).plusWeeks(week - 1);

        try (Connection conn = DBUtil.getConnection();
             PreparedStatement pstmt = conn.prepareStatement(SQL_SELECT_WEEKLY_STATS)) {

            pstmt.setDate(1, new java.sql.Date(weekDate.getMillis()));

            try (ResultSet rs = pstmt.executeQuery()) {
                while (rs.next()) {
                    String status = rs.getString("status");
                    int count = rs.getInt("count");
                    stats.put("total", stats.get("total") + count);
                    if (status.equals(Task.TaskStatus.已完成.name())) {
                        stats.put("completed", count);
                    }
                    else if (status.equals(Task.TaskStatus.延迟.name())) {
                        stats.put("overdue", count);
                    }
                }
            }
        } catch (SQLException e) {
            throw new RuntimeException("Database error while fetching weekly stats.", e);
        }
        return stats;
    }

    //将所有任务数据备份到指定文件路径，格式为CSV。
    @Override
    public void backupData(String filePath) {
        Set<Task> allTasks = getAllTasksSortedByTime();
        DateTimeFormatter formatter = DateTimeFormat.forPattern("yyyy-MM-dd HH:mm:ss");

        try (FileWriter writer = new FileWriter(filePath)) {
            // CSV Header
            writer.write("ID,Title,Type,Priority,Start Time,End Time,Status\n");

            for (Task task : allTasks) {
                String line = String.join(",",
                        String.valueOf(task.getId()),
                        task.getTitle(),
                        task.getTaskType().name(),
                        task.getPriority().name(),
                        task.getStartTime().toString(formatter),
                        task.getEndTime().toString(formatter),
                        task.getStatus().name()
                );
                writer.write(line + "\n");
            }
            System.out.println("Data backup successful to: " + filePath);
        } catch (IOException e) {
            throw new RuntimeException("Error writing backup file.", e);
        }
    }

    //更新给定的任务信息到数据库。如果任务ID无效或任务对象为空，则抛出异常。

    @Override
    public void updateTask(Task task) {
        // 1. 参数校验：避免无效操作（无ID或任务对象为null）
        if (task == null) {
            throw new RuntimeException("更新失败：待更新的任务对象不能为空");
        }
        if (task.getId() == null || task.getId() <= 0) {
            throw new RuntimeException("更新失败：任务ID不能为空且必须为正整数（从「查看所有任务」中获取有效ID）");
        }

        // 2. SQL语句：更新任务表中指定ID的字段（根据Task类的字段调整，这里包含所有可更新字段）
        String sql = "UPDATE tasks " +
                "SET title = ?, content = ?, type = ?, project_id = ?, priority = ?, " +
                "start_time = ?, end_time = ?, status = ? " +
                "WHERE id = ?"; // 关键：通过ID定位唯一任务

        // 3. 数据库操作：使用try-with-resources自动关闭连接/Statement
        try (Connection conn = DBUtil.getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {

            // 4. 设置SQL参数（顺序必须与SQL中的占位符「?」顺序一致）
            pstmt.setString(1, task.getTitle()); // 任务标题
            pstmt.setString(2, task.getContent()); // 任务详情（允许为null）
            pstmt.setString(3, task.getTaskType().name()); // 任务类型（枚举转字符串存储）
            pstmt.setInt(4, task.getProjectId()); // 所属项目ID
            pstmt.setString(5, task.getPriority().name()); // 优先级（枚举转字符串存储）
            // Joda-Time的DateTime转SQL的Timestamp（时间字段适配）
            pstmt.setTimestamp(6, new Timestamp(task.getStartTime().getMillis()));
            pstmt.setTimestamp(7, new Timestamp(task.getEndTime().getMillis()));
            pstmt.setString(8, task.getStatus().name()); // 任务状态（枚举转字符串存储）
            pstmt.setInt(9, task.getId()); // 任务唯一ID（定位要更新的记录）

            // 5. 执行更新并获取「影响行数」（判断是否更新成功）
            int affectedRows = pstmt.executeUpdate();
            if (affectedRows == 0) {
                // 影响行数为0 → 未找到对应ID的任务
                throw new RuntimeException("更新失败：未找到ID为「" + task.getId() + "」的任务（可能已被删除）");
            }

            // 6. 更新成功（可选：打印日志，方便调试）
            System.out.println("任务更新成功！ID：" + task.getId() + "，更新后的标题：" + task.getTitle());

        } catch (SQLException e) {
            // 7. 捕获数据库异常，封装中文提示（方便定位问题）
            throw new RuntimeException("更新任务时数据库出错：" + e.getMessage(), e);
        }
    }

    //根据指定的一周起始时间，获取该周内的所有任务。

    @Override
    public List<Task> getTasksByWeek(DateTime weekStart) {
        // 1. 创建一个空的列表，用于存放查询结果
        List<Task> tasks = new ArrayList<>();

        // 2. 计算一周的结束时间 (weekStart + 7天 - 1毫秒)
        DateTime weekEnd = weekStart.plusWeeks(1).minusMillis(1);

        // 3. 编写 SQL，查询 startTime 在 [weekStart, weekEnd] 区间内的任务
        String sql = "SELECT * FROM tasks WHERE start_time BETWEEN ? AND ? ORDER BY start_time";

        try (Connection conn = DBUtil.getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {

            // 4. 设置参数
            pstmt.setTimestamp(1, new java.sql.Timestamp(weekStart.getMillis()));
            pstmt.setTimestamp(2, new java.sql.Timestamp(weekEnd.getMillis()));

            // 5. 执行查询
            try (ResultSet rs = pstmt.executeQuery()) {
                // 6. 遍历结果集，将每条记录映射为 Task 对象并添加到列表中
                while (rs.next()) {
                    tasks.add(mapResultSetToTask(rs));
                }
            }
        } catch (SQLException e) {
            // 处理异常
            throw new RuntimeException("Failed to get tasks by week", e);
        }

        // 7. 返回最终的任务列表（即使是空的）
        return tasks;
    }

    //根据指定的一个月起始时间，获取该月内的所有任务。
    @Override
    public List<Task> getTasksByMonth(DateTime monthStart) {
        // 1. 创建一个空的列表，用于存放查询结果
        List<Task> tasks = new ArrayList<>();

        // 2. 计算月份的结束时间 (下个月的第一天 - 1毫秒)
        DateTime monthEnd = monthStart.plusMonths(1).minusMillis(1);

        // 3. 编写 SQL，查询 startTime 在 [monthStart, monthEnd] 区间内的任务
        String sql = "SELECT * FROM tasks WHERE start_time BETWEEN ? AND ? ORDER BY start_time";

        try (Connection conn = DBUtil.getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {

            // 4. 设置参数
            pstmt.setTimestamp(1, new java.sql.Timestamp(monthStart.getMillis()));
            pstmt.setTimestamp(2, new java.sql.Timestamp(monthEnd.getMillis()));

            // 5. 执行查询
            try (ResultSet rs = pstmt.executeQuery()) {
                // 6. 遍历结果集，将每条记录映射为 Task 对象并添加到列表中
                while (rs.next()) {
                    tasks.add(mapResultSetToTask(rs));
                }
            }
        } catch (SQLException e) {
            throw new RuntimeException("Failed to get tasks by month", e);
        }

        // 7. 返回最终的任务列表
        return tasks;
    }

    // 获取指定年份和月份的任务统计信息。统计内容包括总任务数、已完成任务数等。
    @Override
    public Map<String, Integer> getMonthlyStats(int year, int month) {
        Map<String, Integer> stats = new HashMap<>();
        stats.put("total", 0);
        stats.put("completed", 0);
        stats.put("overdue", 0);

        // 获取指定月份的任意一天，用于查询
        DateTime monthDate = new DateTime(year, month, 1, 0, 0);

        // 修改SQL查询，使用MONTH和YEAR函数来匹配指定年月的任务
        String sql = "SELECT status, COUNT(*) as count FROM tasks WHERE YEAR(start_time) = ? AND MONTH(start_time) = ? GROUP BY status";

        try (Connection conn = DBUtil.getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {

            pstmt.setInt(1, year);
            pstmt.setInt(2, month);

            try (ResultSet rs = pstmt.executeQuery()) {
                while (rs.next()) {
                    String status = rs.getString("status");
                    int count = rs.getInt("count");
                    stats.put("total", stats.get("total") + count);
                    if (status.equals(Task.TaskStatus.已完成.name())) {
                        stats.put("completed", count);
                    }
                    else if (status.equals(Task.TaskStatus.延迟.name())) {
                        stats.put("overdue", count);
                    }
                }
            }
        } catch (SQLException e) {
            throw new RuntimeException("Database error while fetching monthly stats.", e);
        }
        return stats;
    }


    //将数据库查询结果集映射为Task对象。
    private Task mapResultSetToTask(ResultSet rs) throws SQLException {
        return Task.builder()
                .id(rs.getInt("id"))
                .title(rs.getString("title"))
                .content(rs.getString("content"))
                .taskType(Task.TaskType.valueOf(rs.getString("type")))
                .projectId(rs.getInt("project_id"))
                .priority(Task.Priority.valueOf(rs.getString("priority")))
                .startTime(new DateTime(rs.getTimestamp("start_time")))
                .endTime(new DateTime(rs.getTimestamp("end_time")))
                .status(Task.TaskStatus.valueOf(rs.getString("status")))
                .createdTime(new DateTime(rs.getTimestamp("created_time")))
                .updatedTime(new DateTime(rs.getTimestamp("updated_time")))
                .completedTime(rs.getTimestamp("completed_time") != null ? new DateTime(rs.getTimestamp("completed_time")) : null)
                .build();
    }
}

